<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/12/29
 * Time: 11:34
 */

class Purchase_goods_model extends CI_Model
{

    public $limit_rows = 10;

    public function goods_sku_list($param = array(), $is_all = 0)
    {
        $this->db->select('og.*,gs.sku_num_sn, g.goods_id,g.original_img, g.goods_sn, gs.size,gs.color, s.suppliers_name, po.po_sn');
        $this->db->from('purchase_goods AS og');
        $this->db->join('goods_sku AS gs', 'og.sku_id = gs.sku_id', 'left');
        $this->db->join('goods AS g', 'gs.goods_id = g.goods_id', 'left');
        $this->db->join('suppliers AS s', 'g.suppliers_id = s.suppliers_id', 'left');
        $this->db->join('purchase_order AS po', 'og.po_id = po.po_id', 'left');

        if ($param['admin_id']) {
            $this->db->where('s.user_id', $param['admin_id']);
        }
        if ($param['goods_sn']) {
            $this->db->where('g.goods_sn', $param['goods_sn']);
        }
        if ($param['supp_id']) {
            $this->db->where('g.suppliers_id', $param['supp_id']);
        }

        if ($param['pg_status']) {
            $this->db->where('og.pg_status', $param['pg_status']);
        }

        if ($param['po_id']) {
            $this->db->where('og.po_id', $param['po_id']);
        }

        if ($param['goods_name']) {
            $this->db->where("(og.pg_id = '{$param['goods_name']}' OR gs.sku_num_sn LIKE '{$param['goods_name']}%')");
        }

        if ($is_all == 0) {
            $this->db->limit($this->limit_rows,$this->limit_rows * $param['page']);
        } else {

            if ($param['pg_status'] === 0) {
                $this->db->where('og.pg_status', 0);
            }
        }

        if ($param['order_by']) {
            $this->db->order_by($param['order_by']);
        } else {
            $this->db->order_by('og.pg_number DESC, og.pg_id DESC');
        }

        $query = $this->db->get();
        return $query->result_array();
    }

    public function goods_list($param = array())
    {
        $param['order_by'] = "g.goods_id DESC,gs.color ASC,FIELD(gs.size, 'S','M','L','XL','XXL','XXXL','XXXXL')";
        $goods_sku = $this->goods_sku_list($param, 1);

        $goods = array();
        foreach ($goods_sku as $k => $row) {

            $goods[$row['goods_id']]['goods_sn'] = $row['goods_sn'];
            $goods[$row['goods_id']]['original_img'] = $row['original_img'];
            $goods[$row['goods_id']]['supp_url'] = $row['supp_url'];

            if ($row['pg_status'] > 1) {
                $goods[$row['goods_id']]['status'] = 1;
            }

            $goods[$row['goods_id']]['sku'][$k] = array(
                'pg_id' => $row['pg_id'],
                'sku_attr' => $row['color'] . ' / ' . $row['size'],
                'add_time' => $row['add_time'],
                'pg_status' => $row['pg_status'],
                'inv_number' => $row['inv_number'],
                'pg_number' => $row['pg_number'],
                'pg_info' => $row['pg_info'],
                'supp_url' => $row['supp_url'],
                'purc_price' => $row['purc_price']
            );
        }

        return $goods;
    }

    public function purchase_goods_f()
    {

        $data = "供应商,SKU,Qty,仓库,备注,合同号,采购员,含税单价,物流费,付款方式,制单人,到货日期,1688单号,预付款\n";

        $query = $this->db->from('purchase_goods as pg')
            ->select("pg.*,s.suppliers_name,gs.sku_sn,po.ali_invoice_no,po.po_sn")
            ->join('goods_sku as gs', 'pg.sku_id = gs.sku_id', 'left')
            ->join('purchase_order as po', 'pg.po_id = po.po_id', 'left')
            ->join('suppliers as s', 'po.supp_id = s.suppliers_id', 'left')
            ->where("pg.pg_status = 2 and suppliers_name != ''")
            ->get();

        $list = $query->result_array();

        return $list;
    }


    public function get_goods_sku_list($param)
    {
        $query = $this->db->from('purchase_goods')->where($param)->get();
        $purchase_goods = $query->result_array();

        return $purchase_goods;
    }

    public function unallocated_goods_supp()
    {
        $query = $this->db->from('purchase_goods')->select('supp_id')->where("po_id = 0")->group_by('supp_id')->get();
        return $query->result_array();
    }

    public function goods_log_list($pg_id)
    {
        $this->db->select('pl.*,oi.order_sn,w.web_name,oi.web_id');
        $this->db->from('purchase_goods_log AS pl');
        $this->db->join('order_goods AS og', 'pl.rec_id = og.rec_id', 'left');
        $this->db->join('order_info AS oi', 'og.order_id = oi.order_id', 'left');
        $this->db->join('siteweb AS w', 'oi.web_id = w.web_id', 'left');
        $this->db->where("pl.pg_id = '{$pg_id}'");

        $query = $this->db->get();
        $_arr = $query->result_array();

        foreach ($_arr as $k => $row) {
            $_arr[$k]['add_time'] = date('Y-m-d H:i:s', $row['add_time']);
        }

        return $_arr;
    }

    public function goods_num($param)
    {

        $this->db->from('purchase_goods AS og');
        $this->db->join('goods_sku AS gs', 'og.sku_id = gs.sku_id', 'left');
        $this->db->join('goods AS g', 'gs.goods_id = g.goods_id', 'left');
        $this->db->join('suppliers AS s', 'g.suppliers_id = s.suppliers_id', 'left');
        if ($param['admin_id']) {
            $this->db->where('s.user_id', $param['admin_id']);
        }
        if ($param['goods_sn']) {
            $this->db->where('g.goods_sn', $param['goods_sn']);
        }

        if ($param['pg_status']) {
            $this->db->where('og.pg_status', $param['pg_status']);
        }

        if ($param['goods_name']) {
            $this->db->where("(og.pg_id = '{$param['goods_name']}' OR gs.sku_num_sn LIKE '{$param['goods_name']}%')");
        }

        return $this->db->count_all_results();
    }

    public function goods_info($param, $select = '*')
    {
        $query = $this->db->from('purchase_goods')->select($select)->where($param)->get();

        $_goods = $query->row_array();

        if ($_goods) {
            $query_log = $this->db->from('purchase_goods_log')->where("pg_id = '{$_goods['pg_id']}'")->get();
            $_goods['goods_log'] = $query_log->result_array();
        }

        return $_goods;
    }

    public function get_id_goods_info($param)
    {
        $query = $this->db->from('purchase_goods AS pg')
            ->join('goods_supp AS gs', 'pg.goods_id = gs.goods_id', 'left')
            ->select('pg.*,gs.in_price')
            ->where($param)->get();

        $_goods = $query->row_array();

        return $_goods;
    }
    public function purchase_order_goods($_order_goods = array())
    {
        $time = time();
        $log_info = '';
        $pg_ids = array();
        foreach ($_order_goods as $row) {

            $need_number = $row['goods_number']-$row['occupy_inventory_num'];
            if ($need_number <= 0) { continue;}

            $purchase_goods = $this->get_id_goods_info(array('pg.sku_id' => $row['sku_id'], 'pg.supp_id' => strval($row['supp_id']), 'pg.pg_status <= ' => 1));


            $data = array(
                'pg_id' => $purchase_goods['pg_id'],
                'sku_id' => $row['sku_id'],
                'type' => 1,
                'rec_id' => $row['rec_id'],
                'goods_id' => $row['goods_id'],
                'supp_id' => strval($row['supp_id']),
                'supp_url' => strval($row['supp_url']),
                'add_number' => $need_number,
                'purc_price' => $row['in_price'] ? $row['in_price'] : 0.00,
                'log_info' => $row['log_info'] ? $row['log_info'] : '',
                'add_time' => $time
            );
            $pg_id = $this->add_goods($data);
            
            $this->db->where("rec_id", $row['rec_id']);
            $this->db->update('order_goods', array('status' => 2));
            
            $pg_ids[$row['rec_id']] = array(
                'pg_id' => $pg_id,
                'need_number' => $need_number
            );
        }

        return $pg_ids;
    }
    public function add_goods($data = array())
    {

        if ($data['pg_id']) {
            $pg_id = $data['pg_id'];

            $this->db->where(array('pg_id' => $pg_id));
            $this->db->set('pg_number', 'pg_number+'.$data['add_number'], FALSE);
            $this->db->set('purc_price', 'purc_price+'.($data['add_number'] * $data['purc_price']), FALSE);

            $this->db->where(array('pg_id' => $data['pg_id']))->update('purchase_goods');
        } else {

            $_data = array(
                'sku_id' => $data['sku_id'],
                'goods_id' => $data['goods_id'],
                'supp_id' => intval($data['supp_id']),
                'supp_url' => $data['supp_url'],
                'pg_number' => $data['add_number'],
                'inv_number' => 0,
                'pg_status' => 0,
                'purc_price' => $data['purc_price'] * $data['add_number'],
                'add_time' => $data['add_time'],
                'inv_time' => 0,
                'pg_info' => '',
                'is_wrong' => 0
            );
            $this->db->insert('purchase_goods', $_data);
            $pg_id = $this->db->insert_id();
        }

        
        $data_log = array(
            'type' => intval($data['type']),
            'rec_id' => intval($data['rec_id']),
            'supp_id' => $data['supp_id'],
            'add_number' => $data['add_number'],
            'purc_price' => $data['purc_price'],
            'log_info' => $data['log_info'],
            'add_time' => $data['add_time'],
            'pg_id' => $pg_id
        );

        $this->db->insert('purchase_goods_log', $data_log);

        //

        return $pg_id;
    }

    public function set_po_status($param, $pg_status)
    {

        if ($pg_status == 2) {
            $purchase_goods = $this->get_goods_sku_list($param);
            foreach ($purchase_goods as $row) {
                $this->add_goods_sku_supp(array(
                    'sku_id' => $row['sku_id'],
                    'supp_id' => intval($row['supp_id']),
                    'purchase_number' => $row['pg_number']
                ));
            }
        }

        $this->db->where($param)
            ->set('pg_status', $pg_status)
            ->update('purchase_goods');

        return true;
    }


    public function add_goods_sku_supp($data)
    {
        $query = $this->db->from('goods_sku_storage')->where("supp_id = '{$data['supp_id']}' AND sku_id = '{$data['sku_id']}'")->get();
        $goods_sku_supp = $query->row_array();
    
        /*$timestamp = strtotime(date('Y-m-d', strtotime("this week Monday", time())));
        $query = $this->db->from('purchase_storage_list')->where(array(
            'psl_num' => $timestamp,
            'supp_id' => $data['supp_id']
        ))->get();
        
        $psl_list = $this->row_array();*/

        if ($goods_sku_supp) {

            $this->db->where("sgs_id", $goods_sku_supp['sgs_id']);
            $this->db->set('purchase_number', "purchase_number+{$data['purchase_number']}", false);
            $this->db->update('goods_sku_storage');

        } else {
            
            $this->db->insert('goods_sku_storage', $data);
        }

        return true;
    }
    
    public function set_pg_status($pg_id, $param = array())
    {

        if (isset($param['pg_status'])) {
            $this->db->set('pg_status', $param['pg_status']);
        }

        if ($param['inv_number']) {
            $this->db->set('inv_number', 'inv_number+'. $param['inv_number'], FALSE);
        }

        if ($param['sus_number']) {
            $this->db->set('sus_number', 'sus_number+'. $param['sus_number'], FALSE);
        }


        $this->db->where("pg_id = '{$pg_id}'");
        $this->db->update('purchase_goods');
        return true;

    }
    public function get_need_inventory_goods($sku_id, $supp_id, $psl_id = 0)
    {
    
    
        if ($psl_id) {
            $query = $this->db->from('purchase_storage_list')->where('psl_id', $psl_id)->get();
            $psl_info = $query->row_array();
        }
        
        $this->db->select('pg.*, (pg.pg_number - pg.inv_number) AS need_number');
        $this->db->from('purchase_goods AS pg');
        $this->db->where("pg.sku_id = '{$sku_id}' AND supp_id = '{$supp_id}' AND pg.pg_number > pg.inv_number");
        $this->db->order_by("find_in_set(pg.po_id, '{$psl_info['po_ids']}'), pg.add_time ASC");

        $query = $this->db->get();

        $purchase_goods = $query->result_array();

        return $purchase_goods;
    }
    public function  allot_pg_number($data_storage, $psl_id = 0)
    {
        $surplus_number = $data_storage['storage_number'];
        $add_time = time();
        $po_ids = array();

        $purchase_goods = $this->get_need_inventory_goods($data_storage['sku_id'], $data_storage['supp_id'], $psl_id);

        foreach ($purchase_goods as $row) {
            if ($surplus_number <= 0) {
                $surplus_number = 0;
                break;
            }

            $need_number = $surplus_number >= $row['need_number'] ? $row['need_number'] : $surplus_number;

            //
            $data_log = array(
                'storage_id' => $data_storage['storage_id'],
                'type' => 1,
                'allot_id' => $row['pg_id'],
                'allot_number' => $need_number,
                'add_time' => $add_time
            );
            $this->db->insert('purchase_storage_log', $data_log);


            $this->db->set('inv_number', 'inv_number+'.$need_number, FALSE);
            if ($surplus_number >= $row['need_number']) {
                $this->db->set('pg_status', 3);
            }
            $this->db->where('pg_id', $row['pg_id']);
            $this->db->update('purchase_goods');
            $surplus_number -= $need_number;

            $po_ids[$row['po_id']] = $row['po_id'];
            $pg_ids[$row['pg_id']] = array(
                'po_id' => $row['po_id'],
                'need_number' => $need_number
            );

        }
        $occupy_number = $surplus_number <= 0 ? $data_storage['storage_number'] : ($data_storage['storage_number'] - $surplus_number);

        return array('surplus_number' => $surplus_number, 'occupy_number' => $occupy_number, 'po_ids' => $po_ids, 'pg_ids' => $pg_ids);
    }
    public function purchase_storage($data_storage)
    {

        $this->db->set('storage_number', 'storage_number+'.$data_storage['storage_number'], false);
        $this->db->set('update_time', time());
        $this->db->where(array('sku_id' => $data_storage['sku_id'], 'supp_id' => $data_storage['supp_id']));
        $this->db->update('goods_sku_storage');

        $data_log = array(
            'admin_id' => $data_storage['admin_id'],
            'invoice_no' => $data_storage['invoice_no'],
            'sgs_id' => $data_storage['sgs_id'],
            'sku_id' => $data_storage['sku_id'],
            'supp_id' => $data_storage['supp_id'],
            'storage_number' => $data_storage['storage_number'],
            'add_time' => time()
        );

        $this->db->insert('purchase_storage', $data_log);

        return $this->db->insert_id();
    }
    public function get_purchase_goods_sales($sku_id, $time = array(), $is_inv = 0)
    {
        $this->db->where("pg.sku_id = '{$sku_id}'");

        if ($time['start']) {
            $this->db->where("pgl.add_time >= '{$time['start']}'");
        }

        if ($is_inv) {
            $this->db->where_in("po.po_status", array(2,3));
        }

        if ($time['end']) {
            $this->db->where("pgl.add_time <= '{$time['end']}'");
        }

        $query = $this->db
            ->from('purchase_goods_log AS pgl')
            ->select_sum('pgl.add_number')
            ->join('purchase_goods AS pg', 'pgl.pg_id = pg.pg_id', 'left')
            ->join('purchase_order AS po', 'pg.po_id = po.po_id', 'left')
            ->get();

        $purchase_sum = $query->row_array();

        return intval($purchase_sum['add_number']);
    }
    public function get_purchase_storage_sales($sku_id, $time = array())
    {
        $this->db->where("ps.sku_id = '{$sku_id}'");

        if ($time['start']) {
            $this->db->where("ps.add_time >= '{$time['start']}'");
        }

        if ($time['end']) {
            $this->db->where("ps.add_time <= '{$time['end']}'");
        }

        $query = $this->db
            ->from('purchase_storage AS ps')
            ->select_sum('ps.storage_number')
            ->get();

        $purchase_sum = $query->row_array();

        return intval($purchase_sum['storage_number']);
    }
    public function subtract_order_purchase_number($rec_id, $need_number)
    {
        $query = $this->db->from('purchase_goods_log AS pgl')
            ->select('pg.*,pgl.rec_id,pgl.add_number,pgl.purc_price')
            ->join('purchase_goods AS pg', 'pgl.pg_id = pg.pg_id', 'left')
            ->where("pgl.rec_id = '{$rec_id}' AND pg.pg_status <= 1 AND pgl.type = 1")->get();
        $purchase_goods = $query->row_array();
        if(!$purchase_goods) {
            return false;
        }
        if ($need_number < 0) {
            $need_number = $need_number >= $purchase_goods['add_number'] ? $purchase_goods['add_number'] : $need_number;
        } else {
            $need_number = $purchase_goods['add_number'];
        }
        $this->db->set('pg_number', "pg_number-{$need_number}", false);
        $this->db->where('pg_id', $purchase_goods['pg_id']);
        $this->db->update('purchase_goods');
        $data_log = array(
            'type' => 1,
            'supp_id' => $purchase_goods['supp_id'],
            'pg_id' => $purchase_goods['pg_id'],
            'rec_id' => $rec_id,
            'purc_price' => $purchase_goods['purc_price'],
            'add_number' => -$need_number,
            'log_info' => '',
            'add_time' => time()
        );
        $this->db->insert('purchase_goods_log', $data_log);
        return array('pg_id' => $purchase_goods['pg_id'], 'number' => $need_number);
    }
}
